# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql
from time import sleep
import datetime
class BeautifyerPipeline:
    conn =None
    cursor = None
    dateTamp=None
    host = "192.168.0.113"
    port = 3306
    user='coname'
    password = 'xZS6p4LDcKCwn4Yb'
    db="co"
    charset="utf8"

    
    def open_spider(self,spider):
        try:
            self.conn = pymysql.Connection(host=self.host,port=self.port,user=self.user,password=self.password,db=self.db,charset=self.charset)
            print("连接数据成功！")
        except Exception as e:
            print("连接数据失败!>>>",e)
        self.dateTanp='{0:%m%y%d%H%M%S}'.format(datetime.datetime.now())
        self.dateTamp=int(self.dateTanp) 
        self.n=1
    def process_item(self, item, spider):
        self.cursor= self.conn.cursor()
        sleep(1)
        self.n=self.n+1
        try:
            # if not self.cursor.execute('select chapte from co_books_look_contents where chapte="{}"'.format(item["art_chapte"])):
            if not self.cursor.execute('select srname from co_beautify where srname="{}"'.format(item["art_title"])):
 
                sql = 'insert into co_beautify (content,srname,keywordid,adsrtime) values(""{}"","{}","{}","{}")'.format(item["art_content"],item["art_title"],self.dateTamp+self.n,item["art_time"])
                self.cursor.execute(sql)
                self.conn.commit()
                print('插入成功!')
                return item
            # sql = 'insert into co_books_look_contents(auther) value("{}") where pid="{}"'.format(item["art_pid"],item["art_author"])
            # sql='update  co_books_look_content set author="{}" where chapte="{}"'.format(item["art_author"],item["art_chapte"])
            # sql='update  co_beautify set keywordid="{}" where srname="{}"'.format(self.dateTamp+self.n,item["art_title"])
            # # if self.cursor.execute('select chapte from co_books_look_content where chapte ="{}"'.format(item['art_chapte']))!="[]":
            # self.cursor.execute(sql)
            # print("插入内容成功！")
            # print(item)
            # self.conn.commit()
            # return item
            # else:
            #     self.cursor.execute('update  co_books_look_content set author="{}" where pid="{}"'.format(item["art_author"],item["art_pid"]))
            #     print("插入内容成功！")
            #     print(item)
            #     self.conn.commit()
            #     return item
        except Exception as e:
            print("插入数据错误>>>",e)
            self.conn.rollback()
        # self.cursor.execute("update  co_books_look set fileurl='{}' where srname='{}'".format(item["art_url"],item["art_name"]))
       

    def close_spider(self,spider):
        try:
            self.conn.cursor().close()
            sleep(1)
            self.conn.close()
            print("关闭成功！")
        except Exception as e:
            print("关闭失败>>>",e)